#!/usr/bin/evn python
# -*- coding: utf-8 -*-
"""
-------------------------------------------------
   文件名称 :     core
   文件功能描述 :   功能描述
   创建人 :       小钟同学
   创建时间 :          2021/7/2
-------------------------------------------------
   修改描述-2021/7/2:         
-------------------------------------------------
"""
import asyncpg
import traceback
from .exceptions import ClientConfigBadException
from typing import Optional
from dotmap import DotMap

from .models.insert import Insert
from .models.select import Select
from .models.update import Update
from .models.delete import Delect


# from afast_core.core_libs.ormx.infirmary_models.select import Select
# from afast_core.core_libs.ormx.infirmary_models.insert import Insert
# from afast_core.core_libs.ormx.infirmary_models.update import Update
# from afast_core.core_libs.ormx.infirmary_models.delete import Delect

# DSN = 'postgres://{0}:{1}@{2}:{3}/{4}'.format(db_user, db_pass, db_host, db_port, db_name,)

class ZyxOrm(Select, Insert, Delect, Update):
    def __init__(self, config):
        self.pool: Optional[asyncpg.Pool] = None
        self.config = config

        # 如果配置信息不存在，则抛出异常
        if not self.config:
            raise ClientConfigBadException

    async def connect_pool(self,
                           # 表示URL必须传入
                           # url: str, *,
                           max_queries=50000,
                           # 用来设置一个connection在连接池中的存活时间，默认是1800000，即30分钟。如果设置为0，表示存活时间无限大。如果不等于0且小于30秒则会被重置回30分钟。
                           max_inactive_connection_lifetime=300.0,
                           ):
        try:
            if self.config:
                # 把配置字典转换为 m=DotMap(dict(row1.items()))
                # 创建连接处的对象
                self.pool = await asyncpg.create_pool(min_size=self.config.get('min_size', 5),
                                                      max_size=self.config.get('max_size', 5),
                                                      user=self.config.user,
                                                      host=self.config.host,
                                                      max_queries=max_queries,
                                                      max_inactive_connection_lifetime=max_inactive_connection_lifetime,
                                                      port=self.config.get('port', 5432),
                                                      password=self.config.password,
                                                      database=self.config.database)


            else:
                pass

            return self.pool

        except Exception:
            # 需要手动的捕获异常的异常的信息
            traceback.print_exc()
            pass

    # 在getCurosr方法中是从连接池中重新获取了一个可用的连接。
    async def get_conn(self):
        conn = await self.pool.acquire()
        # short_url = await conn.fetchval('SELECT short_url FROM redirects WHERE original_url = $1;', 3324)
        # latest_links = await conn.fetch('SELECT * FROM redirects ORDER BY created_at DESC LIMIT $1;', LATEST_LINKS)
        # await conn.execute('INSERT INTO redirects (short_url, original_url) VALUES ($1, $2);', short_url, original_url)
        # original_url = await conn.fetchval('SELECT original_url FROM redirects WHERE short_url = $1;', url)
        #  resp = await conn.fetchval('SELECT 1;')
        return conn

    # 释放某个池中某个链接
    async def close_conn(self, conn):
        await self.pool.release(conn)

    # 关闭整个连接池
    async def close_pool(self):
        if self.pool is not None:
            await self.pool.close()
        raise ClientConfigBadException(errmsg='数据库还没连接上！')

    # execute 和execute_many 执行SQL语句，不需要返回记录值！执行效率高，速度快
    async def act_execute(self, *args, sql: str, timeout=None):
        # 连接是从池子里面取出的，上下文结束之后会自动放回到到池子里面
        async with self.pool.acquire() as conn:
            await conn.execute(sql, *args, timeout=timeout)

    # execute 和execute_many 执行SQL语句，不需要返回记录值！执行效率高，速度快
    async def act_execute_many(self, *args, sql: str, timeout=None, isopen_transaction=False):
        # 连接是从池子里面取出的，上下文结束之后会自动放回到到池子里面
        async with self.pool.acquire() as conn:
            if isopen_transaction:
                async with conn.transaction():
                    await conn.executemany(sql, *args, timeout=timeout)
            else:
                await conn.executemany(sql, *args, timeout=timeout)

    # 下面的有数据返回----这种方式不可以执行多条SQL可以避免SQL注入
    async def fetch_all(self, sql=None, *args, isdict=True, timeout=None, isopen_transaction=False):
        # 连接是从池子里面取出的，上下文结束之后会自动放回到到池子里面
        print("SQL:", self.__sql__)
        if not self.__sql__ and sql:
            self.__sql__ = sql

        async with self.pool.acquire() as conn:
            # 调用 await conn.fetch 执行 infirmary_models 语句，获取满足条件的全部记录
            # 结果返回是：一个 Record 对象列表，这个 Record 对象等于将返回的记录进行了一个封装
            if isopen_transaction:
                async with conn.transaction():
                    rows = await conn.fetch(self.__sql__, *args, timeout=timeout)
            else:
                rows = await conn.fetch(self.__sql__, *args, timeout=timeout)
            # return  list(map(dict, rows))if rows else None
            return (list(map(dict, rows)) if isdict else rows) if rows else None

    # ----这种方式不可以执行多条SQL可以避免SQL注入
    async def fetch_get(self, *args, isdict=True, timeout=None, isopen_transaction=False):
        # 连接是从池子里面取出的，上下文结束之后会自动放回到到池子里面
        print("SQL:", self.__sql__)
        if self.__sql__:
            async with self.pool.acquire() as conn:
                #  调用 await conn.fetchrow 执行 infirmary_models 语句，获取满足条件的单条记录
                # 结果返回是：一个 Record 对象，这个 Record 对象等于将返回的记录进行了一个封装
                if isopen_transaction:
                    async with conn.transaction():
                        row = await conn.fetchrow(self.__sql__, *args, timeout=timeout)
                else:
                    row = await conn.fetchrow(self.__sql__, *args, timeout=timeout)
                return (dict(row.items()) if isdict else row) if row else None

        # 下面的有数据返回----这种方式不可以执行多条SQL可以避免SQL注入

    async def raw_sql_fetch_all(self, sql, *args, isdict=True, timeout=None, isopen_transaction=False):
        # 连接是从池子里面取出的，上下文结束之后会自动放回到到池子里面
        return await self.fetch_all(sql=sql, timeout=timeout, isopen_transaction=isopen_transaction)


import asyncio


async def main():
    myorm = ZyxOrm(config=DotMap(min_size=5,
                                 max_size=10,
                                 user='postgres',
                                 host='localhost',
                                 port=5432,
                                 password="123456",
                                 database="zyxadminsystem"))

    await myorm.connect_pool()

    try:

        _restlt = await myorm \
            .tablle('sys_user') \
            .where('id', '=', 1) \
            .where('username', 'like', '%super%') \
            .do_select() \
            .fetch_get()
        print("查询语句-查询全部字段，并返回字典", _restlt)

        _restlt = await myorm \
            .tablle('sys_user') \
            .select('username') \
            .where('id', '=', 1) \
            .where('username', 'like', '%super%') \
            .do_select() \
            .fetch_get()
        print("查询语句-查询某个字典，并返回字典", _restlt)

        _restlt = await myorm \
            .tablle('sys_user') \
            .select('username') \
            .where('id', 'not in', [1, 2]) \
            .do_select() \
            .fetch_all()
        print("查询语句-where的表达式的多种方法：", _restlt)

        _restlt = await myorm \
            .tablle('sys_user_role') \
            .select('sys_user_role.user_id') \
            .select('sys_user.realname') \
            .leftjoin_tuple('sys_user', on=('sys_user_role.user_id', '=', 3)) \
            .leftjoin_tuple('sys_user_depart', on=('sys_user_depart.user_id', 'sys_user.id')) \
            .groupby('sys_user_role.user_id', 'sys_user.realname') \
            .orderby('sys_user.realname', 'desc') \
            .do_select() \
            .fetch_all()
        print("连表查询+分组+排序表达式方法：", _restlt)

        _restlt = await myorm \
            .tablle('sys_user') \
            .select('sys_user_role.user_id') \
            .select('sys_user.*') \
            .leftjoin_tuple(join_tablename='sys_user_role', on=('sys_user_role.user_id', '=', 3)) \
            .leftjoin_tuple(join_tablename='sys_user_depart', on=[('sys_user_role.user_id', '=', 3), ('sys_user_depart.user_id', '=', 'sys_user.id')]) \
            .leftjoin_tuple(join_tablename='sys_user_tenant', on=('sys_user_tenant.name = ?', 'sys_user.username')) \
            .do_select() \
            .fetch_all()
        print('连表查询，某个表的全部字段+连表的某个字段方法', _restlt)

        _restlt = await myorm \
            .tablle('sys_user') \
            .select('max(id),max(create_time)') \
            .where('username', 'like', '%jcg_admin%') \
            .do_select() \
            .fetch_get()
        print('函数的使用！！', _restlt)

        susadsql = myorm \
            .tablle('sys_user') \
            .select('id') \
            .where('create_time', '>=', '2020-07-08 09:12:37') \
            .do_select(isback___sql__=True)

        print('返回SQL语句：-函数的使用！！', susadsql)

        _restlt = myorm \
            .tablle('sys_user') \
            .select('count(id)') \
            .where('id in ({})'.format(susadsql)) \
            .do_select()
        # 查询结果
        _restlt = await _restlt.fetch_get()
        print('子查询：-函数的使用！！', susadsql)

        # # # 批量插入===仅打印SQL---后续调用相关传入到SQL执行器里面
        _restlt = myorm.tablle('sys_user_role').create({'user_id': 34, 'role_id': 324})
        _restlt = myorm.tablle('sys_user_role').create([{'user_id': 34, 'role_id': 324}, {'user_id': 134, 'role_id': 3324}])

        # print('批量插入2：',resiasda.__sql__)
        # 多个值进插入-------字段列表和值列表
        _restlt = myorm.tablle('sys_user_role').insert(['user_id', 'role_id'], [['35', '4554'], ['56', '56']])

        print("删除示例》》》》》" * 5)
        resiasda = myorm.tablle('sys_user_role').where('id', 11).delete()
        print('删除', resiasda.__sql__)

        print("更新》》》》》" * 5)
        resiasda = myorm.tablle('sys_user').where('id', 1).returning('username as nimie').update({'username': "管理员22222", 'password': '1856555555'})
        print('修改', resiasda.__sql__)

        resiasda = myorm.tablle('sys_user').where('id', 1).decrement('sex', 1)  # 字段自减3
        print('decrement', resiasda.__sql__)

        # 乐观锁
        resiasda = myorm \
            .tablle('sys_user') \
            .select('username') \
            .where('id', '=', 1) \
            .where('username', 'like', '%管理员%') \
            .lock_by_for_update() \
            .limit(1) \
            .do_select() \

        print('乐观锁', resiasda.sql())

        print("lmist和偏移量设置！！！")
        # Offset 来表示偏移
        # Limit 限制数量
        resiasda = myorm \
            .tablle('sys_user') \
            .lock_by_for_update() \
            .limit(0) \
            .offset(2) \
            .do_select()

        print('乐观锁aaaaaaaaaa', resiasda.sql())

        resiasda = myorm \
            .tablle('sys_user') \
            .paginate(1, 1) \
            .do_select()
        print('分页查询', resiasda.sql())
        resiasda = await resiasda.fetch_all()

        print('分页查询', resiasda)


    # 数据插入{'user_id':34,'role_id':324}

    except Exception:
        # 需要自己手动的处理捕获错误异常
        traceback.print_exc()


if __name__ == '__main__':
    loop = asyncio.get_event_loop()
    loop.run_until_complete(main())
